<?php

class Budget_manager_model extends CI_Model{

    function insType(){
        $query = $this->db->get('institution_type');
        return $query->result();
    }
    function countryList(){
		$query = $this->db->order_by('country_name','asc');
        $query = $this->db->get('country');
        return $query->result();
    }
    function courseList(){
        $query = $this->db->order_by('course_name','asc');
		$query = $this->db->get('course');
        return $query->result();
    }
    function lifestyleList(){
        $query = $this->db->query("SELECT DISTINCT lifestyle from budget_component WHERE lifestyle != ''");
        return $query->result();
    }
    function getMyBudget($student_id)
    {
        $query = $this->db->query('SELECT * FROM budget b, institution_type it, country c, course co
            WHERE b.inst_type = it.institution_type_id
            AND b.destination_country = c.country_id
            AND b.course_genre = co.course_id
            AND student_id="'.$student_id.'"');
        return $query->result();
    }

    function getMyBudgetSpecific($budget_id){
        $query = $this->db->query("SELECT b.budget_name, b.lifestyle_type, 
            b.inst_type, b.course_genre,
            b.destination_country AS country_id
        FROM budget b
        WHERE b.budget_id = ".$budget_id);
        return $query->result();
    }

    function getMyBudgetSpecificAmount($budget_id){
        $query = $this->db->query("SELECT bc.description, bhbc.amount, bc.ticket_type
        FROM budget_has_budget_component bhbc, budget_component bc
        WHERE bhbc.budget_component_id = bc.budget_component_id
        AND bhbc.budget_id = ".$budget_id." ORDER BY bc.description");
        return $query->result();
    }

    function deleteMyBudget($budget_id){
        $this->db->query('DELETE FROM budget_has_budget_component WHERE budget_id="'.$budget_id.'"');
        $this->db->query('DELETE FROM budget WHERE budget_id="'.$budget_id.'"');
    }

    function updateMyBudget($budget_id){
        $this->load->helper('date');
        $budget_id = $this->input->post('get_budget_id');
        $data = array(
             'budget_name'=>$this->input->post('filename'),
             'date_prepared'=>date('y-m-d h:i:s'),
             'lifestyle_type'=>$this->input->post('lifestyle'),
             'inst_type'=>$this->input->post('sch_type'),
             'course_genre'=>$this->input->post('course'),
             'destination_country'=>$this->input->post('destination'),
            );
        $where = "budget_id = $budget_id";
        $query = $this->db->update('budget', $data, $where);
        return $query;
    }

    function updateMyBudgetComponent($budget_id){
        $this->load->database();
        $delete_query = $this->db->query('DELETE FROM budget_has_budget_component WHERE budget_id='.$budget_id);
        $rent_id = $this->input->post('rent_id');
        $rent_amount = $this->input->post('rent_amount');
        $food_id = $this->input->post('food_id');
        $food_amount = $this->input->post('food_amount');
        $transport_id = $this->input->post('transport_id');
        $transport_amount = $this->input->post('transport_amount');
        $travel_id = $this->input->post('travel_id');
        $travel_amount = $this->input->post('fly_times');
        $query = $this->db->query('INSERT INTO budget_has_budget_component (budget_component_id, budget_id, amount)
            VALUES ("'.$rent_id.'",      "'.$budget_id.'", "'.$rent_amount.'"),
                   ("'.$food_id.'",      "'.$budget_id.'", "'.$food_amount.'"),
                   ("'.$transport_id.'", "'.$budget_id.'", "'.$transport_amount.'"),
                   ("'.$travel_id.'",    "'.$budget_id.'", "'.$travel_amount.'")');
    }

    function getSchoolFee($country, $inst_type, $courseID)
    {
        $this->load->database();
        $query = $this->db->query('SELECT minimum_amount FROM budget_component
            WHERE country="'.$country.'" AND inst_type="'.$inst_type.'" AND course_id="'.$courseID.'"');
        return $query->result();
    }

    function getMonthlyExpenses($lifestyle, $type, $country)
    {
        $this->load->database();
        $query = $this->db->query('SELECT minimum_amount, recommended_amount, maximum_amount, budget_component_id FROM budget_component
            WHERE lifestyle="'.$lifestyle.'" AND description="'.$type.'" AND country='.$country);
        return $query->result();
    }

    function getTicketCost($country, $class, $type, $origin)
    {
        $this->load->database();
        $query = $this->db->query('SELECT minimum_amount, budget_component_id FROM budget_component
            WHERE ((country="'.$country.'" AND country_origin="'.$origin.'")
                OR (country="'.$origin.'" AND country_origin="'.$country.'"))
                AND description="'.$type.'" AND ticket_type="'.$class.'"');
        return $query->result();
    }

    function insertBudgetComponent($id){
        $this->load->database();
        $rent_id = $this->input->post('rent_id');
        $rent_amount = $this->input->post('rent_amount');
        $food_id = $this->input->post('food_id');
        $food_amount = $this->input->post('food_amount');
        $transport_id = $this->input->post('transport_id');
        $transport_amount = $this->input->post('transport_amount');
        $travel_id = $this->input->post('travel_id');
        $travel_amount = $this->input->post('fly_times');
        $query = $this->db->query('INSERT INTO budget_has_budget_component (budget_component_id, budget_id, amount)
            VALUES ("'.$rent_id.'",      "'.$id.'", "'.$rent_amount.'"),
                   ("'.$food_id.'",      "'.$id.'", "'.$food_amount.'"),
                   ("'.$transport_id.'", "'.$id.'", "'.$transport_amount.'"),
                   ("'.$travel_id.'",    "'.$id.'", "'.$travel_amount.'")');
        //return $query->result();
    }
    
    function insertBudget($student_id){
        $this->load->helper('date');
         $data = array(
             'budget_name'=>$this->input->post('filename'),
             'date_prepared'=>date('y-m-d h:i:s'),
             'lifestyle_type'=>$this->input->post('lifestyle'),
             'inst_type'=>$this->input->post('sch_type'),
             'course_genre'=>$this->input->post('course'),
             'student_id'=>$student_id,
             'destination_country'=>$this->input->post('destination'),
            );
          $query=$this->db->insert('budget', $data);
          return $this->db->insert_id();
    }
	
	function get_total_daily($budget_id){
		$queryDE = $this->db->query('SELECT SUM(amount) AS daily_expenses
					FROM budget_has_budget_component bhbc 
					WHERE bhbc.budget_id = "'.$budget_id.'" AND bhbc.amount > 5');
		return $queryDE->result();
	}
	
	function get_total_ticket($budget_id){
		$queryTC = $this->db->query('SELECT bhbc.amount * bc.minimum_amount AS ticket_cost
					FROM budget_has_budget_component bhbc, budget_component bc
					WHERE bhbc.budget_id = "'.$budget_id.'" AND bhbc.budget_component_id = bc.budget_component_id AND bc.description="travel"');
		return $queryTC->result();
	}
	
	function get_total_schFees($budget_id){
		$querySF = $this->db->query('SELECT minimum_amount as schFees
					FROM budget b, budget_component bc
					WHERE b.budget_id = "'.$budget_id.'"
					AND b.inst_type = bc.inst_type
					AND b.course_genre = bc.course_id
					AND b.destination_country = bc.country
					AND bc.description = "schFees"');
		return $querySF->result();
	}
	
}

?>